package diamondRingDao.impl;

import diamondRingDao.DiamondRingDao;
import diamondRingEntity.Collection;
import diamondRingEntity.DiamondRingEntity;
import diamondRingEntity.Shooping;
import tool.BaseDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DiamondRingDaoImpl extends BaseDao implements DiamondRingDao {

    /**
     * 全部商品查询
     * @return
     */
    @Override
    public List<DiamondRingEntity> diamond() {
        ResultSet rs=null;
        List<DiamondRingEntity> diamond=new ArrayList<>();
        DiamondRingEntity diamondRingEntity=null;
        try {
            String sql="SELECT id,goodspath as 路径,goodspath2 AS 路径2,series.diamondseries AS 系列,\n" +
                    "style AS 款式,texture AS 材质,`mode`.purchasemode AS 购买方式,\n" +
                    "shape AS 钻戒形状,givingobject AS 送礼对象,diamondtype.type AS 钻戒类型,\n" +
                    "dia.salesvolume AS 销量,colour,carat AS 重量,diamondprice AS 价格\n" +
                    "FROM diamondmessage AS dia\n" +
                    "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                    "INNER JOIN style ON style.styleid=dia.styleid\n" +
                    "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                    "INNER JOIN `mode` ON `mode`.modeid=dia.modeid\n" +
                    "INNER JOIN shape on shape.shapeid=dia.shapeid\n" +
                    "INNER JOIN givingobject ON givingobject.objectid=dia.objectid\n" +
                    "INNER JOIN diamondtype ON diamondtype.typeid=dia.typeid\n" +
                    "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                    "where goods=1";
            rs=super.executeQuery(sql,null);
            while (rs.next()){
                diamondRingEntity=new DiamondRingEntity();
                diamondRingEntity.setId(rs.getInt(1));
                diamondRingEntity.setGoodsPath(rs.getString(2));
                diamondRingEntity.setGoodsPath2(rs.getString(3));
                diamondRingEntity.setSeries(rs.getString(4));
                diamondRingEntity.setStyle(rs.getString(5));
                diamondRingEntity.setTexture(rs.getString(6));
                diamondRingEntity.setMode(rs.getString(7));
                diamondRingEntity.setShape(rs.getString(8));
                diamondRingEntity.setObject(rs.getString(9));
                diamondRingEntity.setType(rs.getString(10));
                diamondRingEntity.setSalesVolume(rs.getInt(11));
                diamondRingEntity.setColour(rs.getString(12));
                diamondRingEntity.setCarat(rs.getString(13));
                diamondRingEntity.setDiamondPrice(rs.getInt(14));
                diamond.add(diamondRingEntity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return diamond;
    }

    /**
     *
     * @param sales
     * @return
     */
    @Override
    public List<DiamondRingEntity> salesVolumeFind(String sales) {

        ResultSet rs=null;
        List<DiamondRingEntity> diamond=new ArrayList<>();
        DiamondRingEntity diamondRingEntity=null;
        String sql=null;
        try {
            if ("all".equals(sales)){
                sql="SELECT id,goodspath as 路径,goodspath2,series.diamondseries AS 系列,\n" +
                        "style AS 款式,texture AS 材质,`mode`.purchasemode AS 购买方式,\n" +
                        "shape AS 钻戒形状,givingobject AS 送礼对象,diamondtype.type AS 钻戒类型,\n" +
                        "dia.salesvolume AS 销量,colour,carat AS 重量,diamondprice AS 价格\n" +
                        "FROM diamondmessage AS dia\n" +
                        "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                        "INNER JOIN style ON style.styleid=dia.styleid\n" +
                        "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                        "INNER JOIN `mode` ON `mode`.modeid=dia.modeid\n" +
                        "INNER JOIN shape on shape.shapeid=dia.shapeid\n" +
                        "INNER JOIN givingobject ON givingobject.objectid=dia.objectid\n" +
                        "INNER JOIN diamondtype ON diamondtype.typeid=dia.typeid\n" +
                        "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                        "where goods=1\n" +
                        "GROUP BY dia.salesvolume DESC";
                        rs=this.executeQuery(sql,null);
            }else {
                sql="SELECT id,goodspath as 路径,goodspath2 AS 路径2,series.diamondseries AS 系列,\n" +
                        "style AS 款式,texture AS 材质,`mode`.purchasemode AS 购买方式,\n" +
                        "shape AS 钻戒形状,givingobject AS 送礼对象,diamondtype.type AS 钻戒类型,\n" +
                        "dia.salesvolume AS 销量,colour,carat AS 重量,diamondprice AS 价格\n" +
                        "FROM diamondmessage AS dia\n" +
                        "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                        "INNER JOIN style ON style.styleid=dia.styleid\n" +
                        "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                        "INNER JOIN `mode` ON `mode`.modeid=dia.modeid\n" +
                        "INNER JOIN shape on shape.shapeid=dia.shapeid\n" +
                        "INNER JOIN givingobject ON givingobject.objectid=dia.objectid\n" +
                        "INNER JOIN diamondtype ON diamondtype.typeid=dia.typeid\n" +
                        "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                        "WHERE series.diamondseries like ? and goods=1\n" +
                        "GROUP BY dia.salesvolume DESC";
                        Object[] param={"%"+sales+"%"};
                        rs=super.executeQuery(sql,param);
            }
            while (rs.next()){
                diamondRingEntity=new DiamondRingEntity();
                diamondRingEntity.setId(rs.getInt(1));
                diamondRingEntity.setGoodsPath(rs.getString(2));
                diamondRingEntity.setGoodsPath2(rs.getString(3));
                diamondRingEntity.setSeries(rs.getString(4));
                diamondRingEntity.setStyle(rs.getString(5));
                diamondRingEntity.setTexture(rs.getString(6));
                diamondRingEntity.setMode(rs.getString(7));
                diamondRingEntity.setShape(rs.getString(8));
                diamondRingEntity.setObject(rs.getString(9));
                diamondRingEntity.setType(rs.getString(10));
                diamondRingEntity.setSalesVolume(rs.getInt(11));
                diamondRingEntity.setColour(rs.getString(12));
                diamondRingEntity.setCarat(rs.getString(13));
                diamondRingEntity.setDiamondPrice(rs.getInt(14));
                diamond.add(diamondRingEntity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return diamond;
    }


    @Override
    public List<DiamondRingEntity> priceFind(String price) {
        ResultSet rs=null;
        List<DiamondRingEntity> diamond=new ArrayList<>();
        DiamondRingEntity diamondRingEntity=null;
        String sql=null;
        try {
            if ("all".equals(price)|| price==null){
                sql="SELECT id,goodspath as 路径,goodspath2 AS 路径2,series.diamondseries AS 系列,\n" +
                        "style AS 款式,texture AS 材质,`mode`.purchasemode AS 购买方式,\n" +
                        "shape AS 钻戒形状,givingobject AS 送礼对象,diamondtype.type AS 钻戒类型,\n" +
                        "dia.salesvolume AS 销量,colour,carat AS 重量,diamondprice AS 价格\n" +
                        "FROM diamondmessage AS dia\n" +
                        "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                        "INNER JOIN style ON style.styleid=dia.styleid\n" +
                        "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                        "INNER JOIN `mode` ON `mode`.modeid=dia.modeid\n" +
                        "INNER JOIN shape on shape.shapeid=dia.shapeid\n" +
                        "INNER JOIN givingobject ON givingobject.objectid=dia.objectid\n" +
                        "INNER JOIN diamondtype ON diamondtype.typeid=dia.typeid\n" +
                        "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                        "where goods=1\n" +
                        "GROUP BY diamondprice DESC";
                rs=this.executeQuery(sql,null);
            }else {
                sql="SELECT id,goodspath as 路径,goodspath2 AS 路径2,series.diamondseries AS 系列,\n" +
                        "style AS 款式,texture AS 材质,`mode`.purchasemode AS 购买方式,\n" +
                        "shape AS 钻戒形状,givingobject AS 送礼对象,diamondtype.type AS 钻戒类型,\n" +
                        "dia.salesvolume AS 销量,colour,carat AS 重量,diamondprice AS 价格\n" +
                        "FROM diamondmessage AS dia\n" +
                        "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                        "INNER JOIN style ON style.styleid=dia.styleid\n" +
                        "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                        "INNER JOIN `mode` ON `mode`.modeid=dia.modeid\n" +
                        "INNER JOIN shape on shape.shapeid=dia.shapeid\n" +
                        "INNER JOIN givingobject ON givingobject.objectid=dia.objectid\n" +
                        "INNER JOIN diamondtype ON diamondtype.typeid=dia.typeid\n" +
                        "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                        "WHERE series.diamondseries like ? and goods=1\n" +
                        "GROUP BY diamondprice DESC";
                Object[] param={"%"+price+"%"};
                rs=super.executeQuery(sql,param);
            }
            while (rs.next()){
                diamondRingEntity=new DiamondRingEntity();
                diamondRingEntity.setId(rs.getInt(1));
                diamondRingEntity.setGoodsPath(rs.getString(2));
                diamondRingEntity.setGoodsPath2(rs.getString(3));
                diamondRingEntity.setSeries(rs.getString(4));
                diamondRingEntity.setStyle(rs.getString(5));
                diamondRingEntity.setTexture(rs.getString(6));
                diamondRingEntity.setMode(rs.getString(7));
                diamondRingEntity.setShape(rs.getString(8));
                diamondRingEntity.setObject(rs.getString(9));
                diamondRingEntity.setType(rs.getString(10));
                diamondRingEntity.setSalesVolume(rs.getInt(11));
                diamondRingEntity.setColour(rs.getString(12));
                diamondRingEntity.setCarat(rs.getString(13));
                diamondRingEntity.setDiamondPrice(rs.getInt(14));
                diamond.add(diamondRingEntity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return diamond;
    }

    @Override
    public List<DiamondRingEntity> seriesFind(String series) {
        ResultSet rs=null;
        List<DiamondRingEntity> diamond=new ArrayList<>();
        DiamondRingEntity diamondRingEntity=null;
        try {
            String sql="SELECT id,goodspath as 路径,goodspath2 AS 路径2,series.diamondseries AS 系列,\n" +
                    "style AS 款式,texture AS 材质,`mode`.purchasemode AS 购买方式,\n" +
                    "shape AS 钻戒形状,givingobject AS 送礼对象,diamondtype.type AS 钻戒类型,\n" +
                    "dia.salesvolume AS 销量,colour,carat AS 重量,diamondprice AS 价格\n" +
                    "FROM diamondmessage AS dia\n" +
                    "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                    "INNER JOIN style ON style.styleid=dia.styleid\n" +
                    "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                    "INNER JOIN `mode` ON `mode`.modeid=dia.modeid\n" +
                    "INNER JOIN shape on shape.shapeid=dia.shapeid\n" +
                    "INNER JOIN givingobject ON givingobject.objectid=dia.objectid\n" +
                    "INNER JOIN diamondtype ON diamondtype.typeid=dia.typeid\n" +
                    "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                    "WHERE series.diamondseries=? and goods=1\n" +
                    "GROUP BY diamondprice DESC";
            Object[] param={series};
            rs=this.executeQuery(sql,param);
            while (rs.next()){
                diamondRingEntity=new DiamondRingEntity();
                diamondRingEntity.setId(rs.getInt(1));
                diamondRingEntity.setGoodsPath(rs.getString(2));
                diamondRingEntity.setGoodsPath2(rs.getString(3));
                diamondRingEntity.setSeries(rs.getString(4));
                diamondRingEntity.setStyle(rs.getString(5));
                diamondRingEntity.setTexture(rs.getString(6));
                diamondRingEntity.setMode(rs.getString(7));
                diamondRingEntity.setShape(rs.getString(8));
                diamondRingEntity.setObject(rs.getString(9));
                diamondRingEntity.setType(rs.getString(10));
                diamondRingEntity.setSalesVolume(rs.getInt(11));
                diamondRingEntity.setColour(rs.getString(12));
                diamondRingEntity.setCarat(rs.getString(13));
                diamondRingEntity.setDiamondPrice(rs.getInt(14));
                diamond.add(diamondRingEntity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return diamond;
    }

    @Override
    public List<DiamondRingEntity> vagueFind(String vague) {
        ResultSet rs=null;
        List<DiamondRingEntity> diamond=new ArrayList<>();
        DiamondRingEntity diamondRingEntity=null;
        String sql=null;
        try {
                sql="SELECT id,goodspath as 路径,goodspath2 AS 路径2,series.diamondseries AS 系列,\n" +
                        "style AS 款式,texture AS 材质,`mode`.purchasemode AS 购买方式,\n" +
                        "shape AS 钻戒形状,givingobject AS 送礼对象,diamondtype.type AS 钻戒类型,\n" +
                        "dia.salesvolume AS 销量,colour,carat AS 重量,diamondprice AS 价格\n" +
                        "FROM diamondmessage AS dia\n" +
                        "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                        "INNER JOIN style ON style.styleid=dia.styleid\n" +
                        "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                        "INNER JOIN `mode` ON `mode`.modeid=dia.modeid\n" +
                        "INNER JOIN shape on shape.shapeid=dia.shapeid\n" +
                        "INNER JOIN givingobject ON givingobject.objectid=dia.objectid\n" +
                        "INNER JOIN diamondtype ON diamondtype.typeid=dia.typeid\n" +
                        "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                        "WHERE goods=1 AND series.diamondseries LIKE ? \n" +
                        "OR style LIKE ? \n" +
                        "OR texture LIKE ?\n" +
                        "OR `mode`.purchasemode LIKE ?\n" +
                        "OR shape LIKE ?\n" +
                        "OR givingobject LIKE ?\n" +
                        "OR diamondtype.type LIKE ?\n" +
                        "OR colour LIKE ?";
                Object[] param={"%"+vague+"%","%"+vague+"%","%"+vague+"%","%"+vague+"%","%"+vague+"%","%"+vague+"%","%"+vague+"%","%"+vague+"%"};
                rs=super.executeQuery(sql,param);
            while (rs.next()){
                diamondRingEntity=new DiamondRingEntity();
                diamondRingEntity.setId(rs.getInt(1));
                diamondRingEntity.setGoodsPath(rs.getString(2));
                diamondRingEntity.setGoodsPath2(rs.getString(3));
                diamondRingEntity.setSeries(rs.getString(4));
                diamondRingEntity.setStyle(rs.getString(5));
                diamondRingEntity.setTexture(rs.getString(6));
                diamondRingEntity.setMode(rs.getString(7));
                diamondRingEntity.setShape(rs.getString(8));
                diamondRingEntity.setObject(rs.getString(9));
                diamondRingEntity.setType(rs.getString(10));
                diamondRingEntity.setSalesVolume(rs.getInt(11));
                diamondRingEntity.setColour(rs.getString(12));
                diamondRingEntity.setCarat(rs.getString(13));
                diamondRingEntity.setDiamondPrice(rs.getInt(14));
                diamond.add(diamondRingEntity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return diamond;
    }

    @Override
    public DiamondRingEntity goodsNews(int id) {
        ResultSet rs=null;
        List<DiamondRingEntity> diamond=new ArrayList<>();
        DiamondRingEntity diamondRingEntity=null;
        String sql=null;
        try {
            sql="SELECT dia.goodspath AS 图片路劲,dia.goodspath2 AS 图片路劲2,\n" +
                    "series.diamondseries AS 系列, style.style as 款式,dia.diamondprice AS 商品价格,dia.sellnum AS 售出数量,\n" +
                    "diamondnum as 钻石数量,caratweight as 单钻重量,texture AS 钻戒材质,colour AS 颜色,\n" +
                    "COUNT(collectionid) as 收藏数量,\n" +
                    "COUNT(recordid) AS 评价数量\n" +
                    "FROM diamondmessage AS dia\n" +
                    "INNER JOIN style ON style.styleid=dia.styleid\n" +
                    "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                    "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
                    "INNER JOIN colour ON colour.colourid=dia.colorid\n" +
                    "INNER JOIN collection ON collection.collectionid=dia.id\n" +
                    "INNER JOIN record ON record.diamondmessagaeid=dia.id\n" +
                    "WHERE dia.id=?";
            Object[] param={id};
            rs=this.executeQuery(sql,param);
            while (rs.next()){
                    diamondRingEntity=new DiamondRingEntity();
                    diamondRingEntity.setGoodsPath(rs.getString(1));
                    diamondRingEntity.setGoodsPath2(rs.getString(2));
                    diamondRingEntity.setSeries(rs.getString(3));
                    diamondRingEntity.setStyle(rs.getString(4));
                    diamondRingEntity.setDiamondPrice(rs.getInt(5));
                    diamondRingEntity.setSellNum(rs.getInt(6));
                    diamondRingEntity.setDiamondNum(rs.getInt(7));
                    diamondRingEntity.setCaratWeight(rs.getString(8));
                    diamondRingEntity.setTexture(rs.getString(9));
                    diamondRingEntity.setColour(rs.getString(10));
                    diamondRingEntity.setColleCtionid(rs.getInt(11));
                    diamondRingEntity.setRecordid(rs.getInt(12));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return diamondRingEntity;
    }

    @Override
    public List<DiamondRingEntity> recordFind(int id) {
        ResultSet rs=null;
        List<DiamondRingEntity> diamond=new ArrayList<>();
        DiamondRingEntity diamondRingEntity=null;
        String sql=null;
        try {
            sql="SELECT recordcontent,recorddate,users.loginName\n" +
                    "FROM diamondmessage as dia\n" +
                    "INNER JOIN record ON dia.id=record.diamondmessagaeid\n" +
                    "INNER JOIN users ON record.userid=users.userId\n" +
                    "WHERE dia.id=?";
            Object[] param={id};
            rs=super.executeQuery(sql,param);
            while (rs.next()){
                diamondRingEntity=new DiamondRingEntity();
                diamondRingEntity.setRecordContent(rs.getString(1));
                diamondRingEntity.setRecordDate(rs.getString(2));
                diamondRingEntity.setLoginName(rs.getString(3));
                diamond.add(diamondRingEntity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return diamond;
    }

    @Override
    public int delGoods(int id) {
        String sql=null;
        int count=0;
            sql="DELETE FROM shoppingcart WHERE shoppingcart.cartid=?";
            Object[] param={id};
            count=this.executeUpdate(sql,param);
        return count;
    }

    @Override
    public int EmptyGoods(int userid) {
        int count=0;
        String sql="DELETE FROM shoppingcart where userid=?";
        Object[] param={userid};
        count=this.executeUpdate(sql,param);
        return count;
    }

    @Override
    public List<Shooping> findGoods(int userId) {
        ResultSet rs=null;
        String sql=null;
        Shooping shooping=null;
        DiamondRingEntity goods=null;
        List<Shooping> shoopings=new ArrayList<>();
        try {
            sql="SELECT sg.cartid,dia.goodspath,series.diamondseries,style.style,texture.texture,dia.diamondprice,sg.quantity,sg.size,sg.lettering,goodsId\n" +
            "FROM shoppingcart AS sg\n" +
            "INNER JOIN users ON users.userId=sg.userid\n" +
            "INNER JOIN diamondmessage as dia ON dia.id=sg.goodsId\n" +
            "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
            "INNER JOIN style ON style.styleid=dia.styleid\n" +
            "INNER JOIN texture ON texture.textureid=dia.textureid\n" +
            "WHERE sg.userid=?";
            Object[] param={userId};
            rs=this.executeQuery(sql,param);
            while (rs.next()){
                shooping=new Shooping();
                goods=new DiamondRingEntity();
                shooping.setCartId(rs.getInt(1));
                goods.setGoodsPath(rs.getString(2));
                goods.setSeries(rs.getString(3));
                goods.setStyle(rs.getString(4));
                goods.setTexture(rs.getString(5));
                goods.setDiamondPrice(rs.getInt(6));
                shooping.setGoods(goods);
                shooping.setQuantity(rs.getInt(7));
                shooping.setSize(rs.getInt(8));
                shooping.setLettering(rs.getString(9));
                shooping.setGoodsId(rs.getInt(10));
                shoopings.add(shooping);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return shoopings;
    }

    @Override
    public int insertShoopingGoods(int userId, int id,int quantity) {
        int count=0;
        int num=0;
        ResultSet rs=null;
        String sql=null;
        try {
            sql="SELECT shoppingcart.goodsId FROM shoppingcart WHERE shoppingcart.goodsId=? and userid=?";
            Object[] param={id,userId};
            rs=this.executeQuery(sql,param);
            while (rs.next()){
                num=rs.getInt(1);
            }
            if (num>0){
                count=0;
            }else {
                sql="INSERT into shoppingcart(shoppingcart.userid,shoppingcart.goodsId,shoppingcart.quantity) VALUES(?,?,?)";
                Object[] param2={userId,id,quantity};
                count=this.executeUpdate(sql,param2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    @Override
    public int updateGoods(int size,String lettering, int id) {
        int count= 0;
        try {
            count = 0;
            String sql="UPDATE shoppingcart SET size=?,lettering=? WHERE shoppingcart.cartid=?";
            Object[] param={size,lettering,id};
            count=super.executeUpdate(sql,param);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public int fingShoppingNum(int userid) {
        int count=0;
        ResultSet rs=null;
        try {
            String sql="SELECT COUNT(shoppingcart.cartid)\n" +
                    "FROM shoppingcart\n" +
                    "WHERE shoppingcart.userid=?";
            Object[] param={userid};
            rs=this.executeQuery(sql,param);
            while (rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    @Override
    public List<Collection> collectionFind(int collId) {
        List<Collection> collectionList=new ArrayList<>();
        ResultSet rs=null;
        try {
            String sql="SELECT dia.goodspath,series.diamondseries,style.style,dia.diamondprice,collection.collectionDate,collection.id,collection.collectionid\n" +
                    "FROM collection\n" +
                    "INNER JOIN diamondmessage AS dia on dia.id=collection.collectionid\n" +
                    "INNER JOIN series ON series.seriesid=dia.seriesid\n" +
                    "INNER JOIN style ON style.styleid=dia.styleid\n" +
                    "WHERE collection.userid=?";
            Object[] param={collId};
            rs=this.executeQuery(sql,param);
            while (rs.next()){
                Collection collection=new Collection();
                DiamondRingEntity diamondRingEntity=new DiamondRingEntity();
                diamondRingEntity.setGoodsPath(rs.getString(1));
                diamondRingEntity.setSeries(rs.getString(2));
                diamondRingEntity.setTexture(rs.getString(3));
                diamondRingEntity.setDiamondPrice(rs.getInt(4));
                collection.setCollectionDate(rs.getString(5));
                collection.setId(rs.getInt(6));
                collection.setCollectionid(rs.getInt(7));
                collection.setGoods(diamondRingEntity);
                collectionList.add(collection);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return collectionList;
    }

    @Override
    public int collectionDelete(int id) {
        int count=0;

        try {
            String sql="DELETE from collection WHERE collection.id=?";
            Object[] param={id};
            count=this.executeUpdate(sql,param);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public int collection(int userid, int goodsid) {
        ResultSet rs=null;
        Object[] param={userid,goodsid};
        int id=0;
        int count=0;
        try {
            String sqlSele="SELECT COUNT(collection.id)\n" +
                    "FROM collection WHERE collection.userid=? and collection.collectionid=?";
            rs=this.executeQuery(sqlSele,param);
            while (rs.next()){
                id=rs.getInt(1);
            }
            if (id>0){
                count=2;
            }else {
                String sqlInsert="INSERT into collection(collection.userid,collection.collectionid)\n" +
                        "VALUES (?,?)";
                count=this.executeUpdate(sqlInsert,param);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public List<DiamondRingEntity> finddiamond() {
        return null;
    }
}
